package com.maycurobj.repository.oa;
import com.maycurobj.entity.oa.ContractManagementEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ContractManagementRepository extends  JpaRepository<ContractManagementEntity,String> {
    @Query(nativeQuery = true, value = "SELECT '您有以下合同已盖章，还未提交给行政：' || LISTAGG(LIUCHENGBH || ' + ' || WENJIANMC, '，') WITHIN GROUP (ORDER BY LIUCHENGBH) || '，请尽快提交。' AS remind, hr.MOBILE as shenqingr FROM formtable_main_273 LEFT JOIN WORKFLOW_REQUESTBASE ON formtable_main_273.REQUESTID = WORKFLOW_REQUESTBASE.REQUESTID, hrmresource hr WHERE WORKFLOW_REQUESTBASE.currentnodeid = 9981 AND hr.id = formtable_main_273.shenqingr and hr.STATUS in (1,0) GROUP BY hr.MOBILE HAVING MAX(TO_DATE(lastoperatedate, 'YYYY-MM-DD')) < ADD_MONTHS(SYSDATE, -1)")
    List<ContractManagementEntity> getContractManagement();

}
